/* 
 *  Copyright 2012 CodeMagi, Inc.
 * 
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */
package com.codemagi.database;

import com.codemagi.util.*;
import java.sql.*;
import java.util.*;
import org.apache.log4j.Logger;

/**
 * Supports SELECT only, returns query results as a com.codemagi.util.OrderedTable. 
 * The first field of a SELECT statement becomes the key, the second field is the value
 *
 * @version 1.0
 * @author August Detlefsen for CodeMagi, Inc.
 */
public class TableDataBean {

    Logger log = Logger.getLogger(this.getClass());
	
    // Used to figure out if the query is a select 
    protected static final String SELECT = "SELECT";
	  
    // The JDBC result set
    protected ResultSet resultSet = null;
	
    // The JDBC result's meta data 
    protected ResultSetMetaData resultSetMetaData = null;
	
    // Connection arguments for the db connection. 
    protected Properties connArgs = new Properties();
	
    // The JDBC connection string. 
    protected String connectString = null;
	
    // The db query.
    protected String query = null;
	
    // The result set of the db query represented as a OrderedTable
    protected OrderedTable queryResult = null;
	
    // The maximum number of rows that the result set will contain.
    private static final int MAX_ROWS = Integer.MAX_VALUE;
    
    // boolean to determine whether to return null or "" if a column is empty
    private boolean returnNulls = true;

    //determines whether to output SQL statements as they are executed
    private boolean showSql = false; 

    //determines whether to output timing info (to System.out)
    private boolean showTiming = false;

    /**
     * standard constructor
     *
     * @param user database user
     * @param password database password
     * @param connectString standard JDBC compliant database connect string
     * @param driver JDBC Driver path
     */
    public TableDataBean(String user, String password, String connectString, String driver) {
	log.debug("Constructing TableDataBean");
        setDbParams(user, password, connectString, driver);
    }
	
    
    /**
     * Convenience constructor takes a Properties object containing the user, password, 
     * connect string and driver. 
     */
    public TableDataBean(Properties dbProperties) {
	
	String user    = dbProperties.getProperty("user");
	String pass    = dbProperties.getProperty("password");
	String connect = dbProperties.getProperty("connectString");
	String driver  = dbProperties.getProperty("driver");
	
	setDbParams(user, pass, connect, driver);
    }


    /**
     * Null constructor required for Bean-ness
     */
    public TableDataBean() {
		//nada
    }


    /**
     * Sets any necessary DB params for this TableDataBean object
     */
    public void setDbParams(String user, String pass, String connect, String driver) {
	
	//check required fields
	String[] requiredFields = { user, pass, connect, driver };
	if ( Utils.isEmpty(requiredFields) ) {
	    System.out.println("WARNING: TableDataBean config data missing");
	}
	
	try {
	    // load DB driver 
	    Class.forName(driver);
	} catch (Exception e) {
	    System.out.println("WARNING: TableDataBean could not load driver: " + driver );
	    log.debug("", e);
	}
	
	connArgs.setProperty("user", user);
	connArgs.setProperty("password", pass);
	
	this.connectString = connect;
    }
    

    /**
     * Determines whether or not to return null from getItem methods. 
     * If set to true, nulls will be returned if there was no value in the column. 
     * If set to false, "" will be returned instead.
     * 
     * @param newValue   boolean to determine whether or not to return nulls
     */
    public void setReturnNulls(boolean newValue) {
		returnNulls = newValue;
    }


    /**
     * Determines whether to output SQL statements to System.out as they are executed
     *
     * @param newValue    If true, SQL statements will be written to System.out as executed
     */
    public void setShowSql(boolean newValue) { 
	showSql = newValue;
    }


    /**
     * Determines whether to output timing info to System.out
     *
     * @param newValue    If true, timing info will be written to System.out as executed
     */
    public void setShowTiming(boolean newValue) { 
	showTiming = newValue;
    }


    /**
     * main method FOR TESTING ONLY!
     */
    public static void main(String args[]) {
	try {
	    System.out.print("attempting to construct TableDataBean... ");
	    TableDataBean db = new TableDataBean("energies", "Ed4aW$$", "jdbc:postgresql:energies", "org.postgresql.Driver");
	    db.setShowTiming(true);
	    db.setShowSql(true);
	    System.out.println("SUCCESS!");

	    
	    System.out.println("attempting select");

	    db.setQuery("SELECT company_id, company_name FROM company");
	    System.out.println(db.getQuery() );

	    db.executeQuery();

	    System.out.println("SELECT complete");

	    OrderedTable result = db.getQueryResult();

	    Enumeration keys = result.keys();
	    while (keys.hasMoreElements()) {
		String key   = (String)keys.nextElement();
		String value = (String)result.get(key);

		System.out.println(key + " - " + value);
	    }

	    System.out.println("DONE");

	} catch (Exception e) {
	    System.out.println("FAILED:");
	    e.printStackTrace();
	}
    }



   /**
     * Execute the query. Throw an exception is bad things happen.
     *
     * @throws SQLException when bad things happen
     */
    public void executeQuery() {
	long startTime    = System.currentTimeMillis();
	long intervalTime = startTime;

        resultSet         = null;
    	resultSetMetaData = null;
	queryResult       = null;

        Connection conn = null;
        Statement stmt  = null;

        if ( !Utils.isEmpty(query) ) {
            
            try {
    	        // Get a connection
    	        log.debug("USER: " + connArgs.getProperty("user"));
    	        log.debug("PASS: " + connArgs.getProperty("password"));
    	        log.debug("CONN: " + connectString);
	        
		conn = DriverManager.getConnection(connectString, connArgs);
		log.debug("CONN: Connection Complete");
		
                if ( conn != null ) {
		    
		    if (showTiming) {
			intervalTime = System.currentTimeMillis();
			System.out.println("Timing: got connection in: " + (intervalTime - startTime));
		    }
 
		    conn.setAutoCommit(true);
    	            
    	            log.debug("CONN: creating statement");
		    stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		    log.debug("CONN: done creating statement");
		    
		    if ( stmt != null ) {
			
			log.debug("CONN: setting max rows");
			
			try {
			    stmt.setMaxRows( MAX_ROWS );
			} catch (Exception smr_E) {
			    // Not all databases support this, but try anyways	
			}
			log.debug("CONN: done setting max rows");
			
			//output SQL if we have been instructed to do so
			if (showSql) System.out.println(query);
			    
			// if the query is a select statement, get the results
			if ( query.toUpperCase().trim().indexOf( SELECT ) == 0 ) {
			    log.debug("CONN: SQL is a SELECT statement");
			
			    if (showTiming) {
				System.out.println("Timing: ready to exec query in: " + (System.currentTimeMillis() - intervalTime));
				intervalTime = System.currentTimeMillis();
			    }
    
			    resultSet = stmt.executeQuery( query );
			    log.debug("CONN: query complete");

			    if (showTiming) {
				System.out.println("Timing: query executed in: " + (System.currentTimeMillis() - intervalTime));
				intervalTime = System.currentTimeMillis();
			    }
			    

                            resultSetMetaData = resultSet.getMetaData();
                            log.debug("CONN: result set meta data Complete");

                            if (showTiming) {
                                System.out.println("Timing: got meta data in: " + (System.currentTimeMillis() - intervalTime));
                                intervalTime = System.currentTimeMillis();
                            }
			    

			    // fill the data array 
			    queryResult = createTable();
			    log.debug("CONN: OrderedTable Complete");
			    
			    if (showTiming) {
				System.out.println("Timing: built cache in: " + (System.currentTimeMillis() - intervalTime));
				intervalTime = System.currentTimeMillis();
			    }
			    
			}
			
		    }
		    
		}
		
		
            } catch ( java.sql.SQLException jsqle ) {
            	
            	log.debug("Message: " + jsqle.getMessage()); 
		log.debug("SQLState: " + jsqle.getSQLState()); 
		log.debug("ErrorCode: " + jsqle.getErrorCode()); 
            	
            } finally {
		//close DB connection
		try {
		    log.debug("CONN: Closing connection");
                    stmt.close();
		    conn.close();
		    log.debug("CONN: Connection closed");
                } catch (Exception e) {
                    log.debug("", e);
                }


		if (showTiming) {
		    System.out.println("Timing: query complete in total: " + (System.currentTimeMillis() - startTime));
		}
		


	    }
        }
    }
    


    /**
     * Create an OrderedTable of Strings from the JDBC result set.     
     *
     * @return The OrderedTable representing the result set.
     */
    protected OrderedTable createTable() {
	
	//estimate the number of rows in the result set
	int estNumRows = estimateNumRows();

	log.debug("Estimated number of rows in result: " + estNumRows);

	OrderedTable ret = new OrderedTable(estNumRows, 1);

        String key   = "";
	String value = "";

        while ( nextResSetRow() ) {

	    key   = getRowSetItem( 1 );
	    value = getRowSetItem( 2 );
        	
	    ret.put(key, value);
	}
        
        return ret;
    }

    
    /**
     * Get the item for specified column in the current row of the rowset
     * of data.
     *
     * @param col The column to get the data for.
     *
     * @return A String representing the data, or null if an error occurs.
     */
    private String getRowSetItem( int col ) {
	try {
	    return resultSet.getString( col );
	} catch ( java.sql.SQLException sqle ) {
            log.debug("", sqle);
        }

	return "";
    }

    /*
    private String getRowSetItem( int col ) {
        String buf = null;
        
        try {
	    String dataType = resultSetMetaData.getColumnTypeName(col);
	    if ("CLOB".equals(dataType)) {
		Clob item = resultSet.getClob(col);
		if (item != null) {
		    buf = item.getSubString(1l, (int)item.length());
		} else {
		    buf = null;
		}
	    } else {
		buf = resultSet.getString( col );
	    }
	    
	    
        } catch ( java.sql.SQLException sqle ) {
            sqle.printStackTrace();
        }
        
        return buf;
    }
    */
    
    /**
     * Move the current pointer to the next row in the result set.
     * 
     * @return true if the pointer is moved successfully.
     * @return false if there are no more rows in the result set.
     */
    protected boolean nextResSetRow() {
        try {
	    return resultSet.next();
        } catch ( Exception e ) {
            // nothing on purpose
        }
        
        return false;
    }
    

    /**
     * Estimates the number of rows in the result set
     */
    private int estimateNumRows() {
	int output = 10;

	try {
	    resultSet.last();
	    output = resultSet.getRow();
	    resultSet.beforeFirst();
	} catch (Exception e) {
	    log.debug("", e);
	}

	return output;
    }


    /**
     * Returns the result of the query, as an OrderedTable
     */
    public OrderedTable getQueryResult() {
	return queryResult;
    }

    
    /**
     * Get the connect string that is used to connect to the db.
     * 
     * @return The connect string.
     */
    public String getConnectString() {
        return( connectString );
    }

    
    /**
     * Set the query to be executed by this bean.
     * 
     * @param query The query to be executed by this bean.
     */
    public void setQuery( String query ) {
        this.query = query;
    }

    
    /**
     * Get the current query that is executed by this bean.
     * 
     * @return the query that is executed by this bean.
     */
    public String getQuery() {   
        return query;
    }
    

}




